#此文件定义服务器的数据库处理相关
import pymysql as mysql
import datetime
class talksql():
    def __init__(self):
        pass

    def open(self):
        self.__db = mysql.connect('localhost','root','123456','talk_sch',charset = 'utf8')

    def close(self):
        self.__db.close()

    def commit(self):
        self.__db.commit()
    def clear_person_table(self):
        cur = self.__db.cursor()
        sql = "TRUNCATE TABLE person"
        cur.execute(sql)
        self.commit()

    def clear_family_table(self):
        cur = self.__db.cursor()
        sql = "TRUNCATE TABLE family"
        cur.execute(sql)
        self.commit()

    def clear_familyperson_table(self):
        cur = self.__db.cursor()
        sql = "TRUNCATE TABLE familyperson"
        cur.execute(sql)
        self.commit()

    def clear_personfamily_table(self):
        cur = self.__db.cursor()
        sql = "TRUNCATE TABLE personfamily"
        cur.execute(sql)
        self.commit()

    def clear_leavesays_table(self):
        cur = self.__db.cursor()
        sql = "TRUNCATE TABLE leavesays"
        cur.execute(sql)
        self.commit()

    def clear_all_table(self):
        self.clear_person_table()
        self.clear_family_table()
        self.clear_familyperson_table()
        self.clear_personfamily_table()
        self.clear_leavesays_table()

    def show_person_table(self):
        cur = self.__db.cursor()
        sql = "select * from person"
        cur.execute(sql)
        all = cur.fetchall()
        print("**************person table show*******************")
        print(all)

    def show_family_table(self):
        cur = self.__db.cursor()
        sql = "select * from family"
        cur.execute(sql)
        all = cur.fetchall()
        print("**************family table show*******************")
        print(all)

    def show_familyperson_table(self):
        cur = self.__db.cursor()
        sql = "select * from familyperson"
        cur.execute(sql)
        all = cur.fetchall()
        print("**************familyperson table show*******************")
        print(all)

    def show_personfamily_table(self):
        cur = self.__db.cursor()
        sql = "select * from personfamily"
        cur.execute(sql)
        all = cur.fetchall()
        print("**************personfamily table show*******************")
        print(all)

    def show_leavesays_table(self):
        cur = self.__db.cursor()
        sql = "select * from leavesays"
        cur.execute(sql)
        all = cur.fetchall()
        print("**************leavesays table show*******************")
        print(all)


    def show_all_table(self):
        self.show_person_table()
        self.show_family_table()
        self.show_familyperson_table()
        self.show_personfamily_table()
        self.show_leavesays_table()

    def createmember(self,name):
        cur = self.__db.cursor()
        #初始password
        password = '123456'
        sql = "INSERT INTO member(password,name) VALUES ('%s','%s')" % (password, name)
        cur.execute(sql)
        id = cur.lastrowid
        self.commit()
        return id

    def getmember(self,id):
        cur = self.__db.cursor()
        sql = "select password,name from member where id = %d"%id
        cur.execute(sql)
        member = cur.fetchone()
        return member
        #return data['password'],data['name']

    def createperson(self,name):
        cursor = self.__db.cursor()
        password = '123456'
        sql = "INSERT INTO person(name,password) VALUES ('%s','%s')" % (name, password)
        cursor.execute(sql)
        id = cursor.lastrowid
        self.commit()
        return id,
    def getperson(self,id):
        cursor = self.__db.cursor()
        sql = "select name,password,islogin,lastlogintime from person where idperson = %d" % id
        cursor.execute(sql)
        person = cursor.fetchone()
        return person

    def getallperson_login(self):
        cursor = self.__db.cursor()
        sql = "select idperson,name,password,islogin,lastlogintime from person where islogin = 1"
        cursor.execute(sql)
        persons = cursor.fetchall()
        return persons

    def updateperson_login(self,id,lastlogintime):
        cursor = self.__db.cursor()
        lastlogintime_str = lastlogintime.strftime("%Y-%m-%d %H:%M:%S.") + str(lastlogintime.microsecond)[:6]
        sql = "UPDATE person SET islogin=1,lastlogintime = str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s.%%f') " \
              "WHERE idperson = %d" % (lastlogintime_str,id)
        print(sql)
        cursor.execute(sql)
        id = cursor.lastrowid
        self.commit()
        return id,

    def createfamily(self,name):
        cursor = self.__db.cursor()
        sql = "INSERT INTO family(name) VALUES ('%s')" % name
        cursor.execute(sql)
        id = cursor.lastrowid
        self.commit()
        return id,

    def createfamily_and_addpersons(self,name,personids):
        familyid,= self.createfamily(name)
        for personid in personids:
            self.add_familyperson(familyid,personid)
        return familyid,

    def createfamily_and_which_persons(self,familyname,personnames):
        familyid, =self.createfamily(familyname)
        for personame in personnames:
            personid,= self.createperson(personame)
            self.add_familyperson(familyid, personid)
            self.add_personfamily(personid,familyid)
        return familyid,

    def getfamily(self, id):
        cursor = self.__db.cursor()
        sql = "select name from family where idfamily = %d" % id
        #sql = "select * from family "
        cursor.execute(sql)
        family = cursor.fetchone()
        return family
    def getallfamily(self):
        cursor = self.__db.cursor()
        sql = "select idfamily,name from family "
        cursor.execute(sql)
        familys = cursor.fetchall()
        return familys

    def add_personfamily(self,personid,familyid):
        cursor = self.__db.cursor()
        sql = "INSERT INTO personfamily(personid,familyid) VALUES (%d,%d)" % (personid,familyid)
        cursor.execute(sql)
        self.commit()

    def get_personfamily(self,personid):
        cursor = self.__db.cursor()
        sql = "select familyid from personfamily where personid = %d" % personid
        cursor.execute(sql)
        return cursor.fetchall()

    def add_familyperson(self,familyid,personid):
        cursor = self.__db.cursor()
        sql = "INSERT INTO familyperson(familyid,personid) VALUES (%d,%d)" % (familyid,personid)
        cursor.execute(sql)
        self.commit()

    def get_familyperson(self, familyid):
        cursor = self.__db.cursor()
        sql = "select personid from familyperson where familyid = %d" % familyid
        cursor.execute(sql)
        return cursor.fetchall()


    ''' 参考      
    sql_insert = sql_insert = "INSERT into tablename(exTime) values(str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s'))" % (
    dt.strftime("%Y-%m-%d %H:%M:%S"))
    '''
    def add_leavesays(self,personid,issaysinfamily,familyid,sayswho,says):
        cursor = self.__db.cursor()
        saystime = datetime.datetime.now()
        saystimestr = saystime.strftime("%Y-%m-%d %H:%M:%S.")+str(saystime.microsecond)[:3]

        if issaysinfamily:
            sql = "INSERT INTO leavesays(personid,issaysinfamily,familyid,sayswho,says,saystime) VALUES (%d,%d,%d,%d,'%s'," \
                  "str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s.%%f'))" % (
                  personid, 1, familyid, sayswho, says,
                  saystimestr)#TypeError: not all arguments converted during string formatting
        else:
            sql = "INSERT INTO leavesays(personid,issaysinfamily,sayswho,says,saystime) VALUES (%d,%d,%d,'%s'," \
                  "str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s.%%f'))" % (
                      personid, 0, sayswho, says,
                      saystimestr)
            print("add_leavesays sql = ", sql)

        #! 数据库不能存储笑脸等图标，这里需要修改，将varchar 修改为binary,无字符集限制
        # pymysql.err.InternalError: (1366, "Incorrect string value: '\\xF0\\x9F\\x91\\x8C' for column 'says' at row 1")
        # --20180131 该问题已解决，需要注意的是数据库内部自动支持string到binary的转换，遇到不认识的输出warning，在insert时
        #无需将string转为bytes
        cursor.execute(sql)  #？？？？这里出现lost connect异常Lost connection to MySQL server during query
        self.commit()

    def get_leavesays(self,personid):
        cursor = self.__db.cursor()
        sql = "select sayswho,issaysinfamily,familyid,saystime,says from leavesays where personid = %d" % personid
        cursor.execute(sql)
        return cursor.fetchall()

    def del_leavesays(self,personid):
        cursor = self.__db.cursor()
        sql = "delete from leavesays where personid = %d" % personid
        cursor.execute(sql)
        self.commit()

if __name__ == '__main__':
    mytalksql = talksql()
    mytalksql.open()
    mytalksql.close()
    saystime = datetime.datetime.now()
    saystimestr = saystime.strftime("%Y-%m-%d %H:%M:%S.") + str(saystime.microsecond)[:3]
    print("saystimestr:", saystimestr)
    strtodate = "str_to_date(\'%s\','%%Y-%%m-%%d %%H:%%i:%%s.%%f'))" % saystimestr
    print(strtodate)











